package Dao;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */



import Util.ConSQL;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import javax.swing.JComboBox;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author use
 */
public class DangkiHP {
    ConSQL conn = new ConSQL();
     public void cbbKhoa(JComboBox cbb) throws Exception{
        cbb.removeAllItems();
        Statement st = conn.getCon().createStatement();
        ResultSet rs = st.executeQuery("select tenkhoa from khoa");
        
        while(rs.next()){
            cbb.addItem(rs.getString(1));
        }
    }
     public void cbbnienkhoa(JComboBox cbb) throws Exception{
        cbb.removeAllItems();
        Statement st = conn.getCon().createStatement();
        ResultSet rs = st.executeQuery("select tenNK from nienKhoa");
        
        while(rs.next()){
            cbb.addItem(rs.getString(1));
        }
    }
     public void cbbmonhoc(JComboBox cbb) throws Exception{
        cbb.removeAllItems();
        Statement st = conn.getCon().createStatement();
        ResultSet rs = st.executeQuery("select tenMH from monHoc");
        
        while(rs.next()){
            cbb.addItem(rs.getString(1));
        }
    }
    
    public String layMakhoa(String tenKhoa) throws Exception{
        Statement st = conn.getCon().createStatement();
        ResultSet rs = st.executeQuery("select makhoa from khoa where tenkhoa=N'"+tenKhoa+"';");
        if (rs.next()) return rs.getString(1);        
        return rs.getString(1);        
    }
    public String layTenkhoaByMaSV(String maSV) throws Exception{
        Statement st = conn.getCon().createStatement();
        ResultSet rs = st.executeQuery("select tenkhoa from khoa,lopSH,sinhVien where khoa.makhoa=lopSH.makhoa and lopSH.malopSH=sinhVien.malopSH and sinhVien.maSV='"+maSV+"';");
        if (rs.next()) return rs.getString(1);        
        return rs.getString(1);        
    }
    public String layMaNK(String tenNK) throws Exception{
        Statement st = conn.getCon().createStatement();
        ResultSet rs = st.executeQuery("select maNK from nienkhoa where tenNK='"+tenNK+"';");
        if (rs.next()) return rs.getString(1);        
        return rs.getString(1);        
    }
    public int laySoTC(String maMon) throws Exception{
        Statement st = conn.getCon().createStatement();
        ResultSet rs = st.executeQuery("select soTC from monHoc where maMH='"+maMon+"'");
        if(rs.next()){
            return Integer.parseInt(rs.getString(1));
        }
        return Integer.parseInt(rs.getString(1));
    }
    public boolean trungLopHP(String MaSV,String MaLopHP) throws SQLException, Exception{
        Statement st = conn.getCon().createStatement();
        ResultSet rs = st.executeQuery("select maLopHP from lopHP_SV where maLopHP='"+MaLopHP+"' and maSV='"+MaSV+"';");
        if (rs.next()) return true;
        else return false;
    }
    public String trangthaiDangkiHP() throws Exception{
        Statement st = conn.getCon().createStatement();
        ResultSet rs = st.executeQuery("select dangkiHP from trangThaiChucnang;");
        if(rs.next()){
            return rs.getString(1).toString();
        }
        return rs.getString(1).toString();
    }
    public String trangthaiHuyHP() throws Exception{
        Statement st = conn.getCon().createStatement();
        ResultSet rs = st.executeQuery("select huyHP from trangThaiChucnang;");
        if(rs.next()){
            return rs.getString(1).toString();
        }
        return rs.getString(1).toString();
    }
    public String layTenGiaovienDay(String malopHP) throws Exception{
        String tenGV="";
        Statement st = conn.getCon().createStatement();
        ResultSet rs = st.executeQuery("select maGV from giaoVien_lopHP where malopHP='"+malopHP+"'");
        if(rs.next()){
            String maGV = rs.getString(1).toString();
            Statement st2 = conn.getCon().createStatement();
            ResultSet rs2 = st2.executeQuery("select tenGV from giaoVien where maGV='"+maGV+"'");
            if(rs2.next()){
                tenGV =  rs2.getString(1).toString();
            }
        }
        return tenGV;
    }
//    public void DangkiHP(String MaSV,String MaLopHP) throws SQLException, Exception{
//        Statement st = conn.getConnect().createStatement();
//        int rs = st.executeUpdate("insert into LopHP_SV values('"+MaLopHP+"','"+MaSV+"');");
//    }
    //tao title cho column
    public void createTitle(Vector tableTitle){
        tableTitle.removeAllElements();
        tableTitle.add("Mã lóp học phần");
        tableTitle.add("Tên lớp học phần");
        tableTitle.add("Ngày bắt đầu");
        tableTitle.add("Ngày kết thúc");
        tableTitle.add("Mã Khoa");
        tableTitle.add("Mã niên khóa");
        tableTitle.add("Mã môn");
        tableTitle.add("Số tín chỉ");        
        tableTitle.add("Chỉ tiêu");        
        tableTitle.add("Số đăng kí");
        tableTitle.add("Giáo viên");
    }
    public void createRecord(Vector tableRecord,ResultSet rs) throws SQLException, Exception{
        tableRecord.removeAllElements();
        while(rs.next()){
            Vector data = new Vector();
            data.add(rs.getString(1));
            data.add(rs.getString(2));
            data.add(rs.getString(6));
            data.add(rs.getString(7));
            data.add(rs.getString(3));
            data.add(rs.getString(4));
            data.add(rs.getString(5));
            data.add(laySoTC(rs.getString(5)));
            data.add(rs.getString(8));
            data.add(rs.getString(9));
            data.add(layTenGiaovienDay(rs.getString(1)));
            tableRecord.add(data);
        }
    }
    public JTable timLopHP(String tenKhoa,String tenNK,JTable table,Vector tableRecord,Vector tableTitle) throws Exception{
        tableRecord.removeAllElements();
        tableTitle.removeAllElements();
        
        createTitle(tableTitle);
        
        Statement st = conn.getCon().createStatement();
        ResultSet rs = st.executeQuery("select * from lopHP where makhoa='"+layMakhoa(tenKhoa)+"' and maNK='"+layMaNK(tenNK)+"';");
        
        createRecord(tableRecord, rs);
        table.setModel(new DefaultTableModel(tableRecord, tableTitle));
        return table;
    }
    public JTable timLopHPByMaMH(String maMon,JTable table,Vector tableRecord,Vector tableTitle) throws Exception{
        tableTitle.removeAllElements();
        tableRecord.removeAllElements();
        
        createTitle(tableTitle);
        Statement st = conn.getCon().createStatement();
        ResultSet rs = st.executeQuery("select * from lopHP where maMH='"+maMon+"';");
        
        createRecord(tableRecord, rs);        
        table.setModel(new DefaultTableModel(tableRecord, tableTitle));
        return table;
    }
    public void DangkiHP(String maSV, JTable table, int iDongchon) throws SQLException, Exception{
//        int iDongchon = table.getSelectedRow();
//        Vector vDongchon = (Vector) tableRecord.get(iDongchon);

//        String MalopHp = vDongchon.get(0).toString();
        String MalopHp = (String)table.getModel().getValueAt(iDongchon, 0);
        
        if(checkHp(maSV, MalopHp)==true){
            JOptionPane.showMessageDialog(table, "Bạn đã đăng kí lớp này rồi");
        }
        else{
            Statement st = conn.getCon().createStatement();
            int rs = st.executeUpdate("insert into LopHP_SV values('"+MalopHp+"','"+maSV+"');");
            JOptionPane.showMessageDialog(table, "Đăng kí thành công");
        }
    }
    //kiem tra xem sinh vien nay da dang ki lop hp nay chua
    public boolean checkHp(String maSV,String maLopHP) throws Exception{        
        Statement st = conn.getCon().createStatement();
        ResultSet rs = st.executeQuery("select * from lopHP_SV where maSV='"+maSV+"' and maLopHP='"+maLopHP+"';");
        if(rs.next()){
            return true;
        }
        return false;
    }
}
